﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spWH_IndexUsage]
#-- Purpose:		Copies the index usage activity to the Warehouse
#--	Last Update:	12/01/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spWH_IndexUsage]
AS

--- Declare Local Variables
DECLARE	@sSQL varchar(MAX)

IF EXISTS (SELECT TOP 1 * FROM sys.servers WHERE name = '$(MSXServer)')
  BEGIN
	SELECT	@sSQL = 'DECLARE @ius_server_ssd_id int

--- Find this server in the active list of servers
SELECT	@ius_server_ssd_id = [ssd_id]
FROM	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
WHERE	[ssd_sqlserver_name_ro] = @@SERVERNAME
		AND [ssd_is_disabled] = 0

IF @ius_server_ssd_id IS NULL
  BEGIN
	--- This server was not found in the active list of servers, so find the most recent disabled record
	SELECT		TOP 1 @ius_server_ssd_id = [ssd_id]
	FROM		[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
	WHERE		[ssd_sqlserver_name_ro] = @@SERVERNAME
	ORDER BY	[ssd_id] DESC

	--- A disabled record was found for the server, so re-enable it
	IF @ius_server_ssd_id IS NOT NULL
		UPDATE	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
		SET		[ssd_is_disabled] = 0
		WHERE	[ssd_id] = @ius_server_ssd_id
				AND [ssd_is_disabled] = 1
  END

IF @ius_server_ssd_id IS NOT NULL
  BEGIN
	--- Copy Index Stats
	DELETE FROM [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblStg_IndexUsage]
	WHERE [ius_server_ssd_id] = @ius_server_ssd_id

	INSERT INTO [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblStg_IndexUsage]
	(
		[ius_server_ssd_id],
		[ius_sql_starttime],
		[ius_databaseid],
		[ius_tableid],
		[ius_indexid],
		[ius_type],
		[ius_is_disabled],
		[ius_user_updates],
		[ius_user_uses],
		[ius_system_updates],
		[ius_system_uses],
		[ius_is_pkey],
		[ius_data_space_id]
	)
		SELECT		@ius_server_ssd_id [ius_server_ssd_id],
					[ius_sql_starttime],
					[ius_databaseid],
					[ius_tableid],
					[ius_indexid],
					[ius_type],
					[ius_is_disabled],
					[ius_user_updates],
					[ius_user_uses],
					[ius_system_updates],
					[ius_system_uses],
					[ius_is_pkey],
					[ius_data_space_id]
		FROM		[dbo].[tblMonitor_IndexUsage]

	--- Copy Name - Database, Table, Index
	DELETE FROM [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblStg_DbTblInd_Names]
	WHERE [bti_server_ssd_id] = @ius_server_ssd_id

	INSERT INTO [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblStg_DbTblInd_Names]
	(
		[bti_server_ssd_id],
		[bti_databaseid],
		[bti_tableid],
		[bti_indexid],
		[bti_databasename],
		[bti_tablename],
		[bti_indexname],
		[bti_startdate],
		[bti_enddate]
	)
	SELECT		@ius_server_ssd_id [bti_server_ssd_id],
				[bti_databaseid],
				[bti_tableid],
				[bti_indexid],
				[bti_databasename],
				[bti_tablename],
				[bti_indexname],
				[bti_startdate],
				[bti_enddate]
	FROM		[dbo].[tblMonitor_DbTblInd_Names]
  END
ELSE
	RAISERROR(''This server has not been registered with the MSX Server'', 16, 1)'

	EXEC	(@sSQL)
  END